package gui;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import javax.swing.DefaultListModel;
import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextField;
import javax.swing.SwingConstants;
import javax.swing.SwingWorker;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;

import tools.OptionReader;
import contacts.Adressbuch;
import contacts.Person;

/**
 * Erstellt Fenster um innerhalb der MailFunktion Kontakte hinzuzufuegen
 * 
 * @author frederikroessler
 * 
 */
public class AddContactWindow extends JFrame {

	private static final long serialVersionUID = -5517070643123200750L;
	private Connection connection = Test.getConnection();

	// Texteingabe/anzeige Felder fuer Kontaktdaten in der Detailansicht rechts
	private JTextField textField;
	private JTextField textField_1;
	private JTextField textField_2;
	private JTextField textField_3;
	private JTextField textField_4;
	private JTextField textField_5;
	private JTextField textField_6;
	private JTextField textField_7;
	private JTextField textField_8;
	private JTextField textField_9;
	private JTextField textField_10;
	private JButton btnBearbeiten;
	private String fname_old;
	private String lname_old;
	private String street_old;
	private String zip_old;
	private String town_old;
	private String email_old;
	private String bday_old;
	private String telehome_old;
	private String telework_old;
	private String fax_old;
	private String cell_old;
	@SuppressWarnings("unused")
	private LinkedList<Person> personList = new LinkedList<Person>();
	@SuppressWarnings("rawtypes")
	private LinkedList schondabei = new LinkedList();
	private LinkedList<Person> schondabeimail = new LinkedList<Person>();
	int nexteventid = 0;
	private OptionReader or;
	private String[] windowColorsSeperated;
	private int r;
	private int g;
	private int b;
	// Liste um Personendaten lokal zu speichern
	private LinkedList<Person> contactlinkedlist = new LinkedList<Person>();
	@SuppressWarnings("rawtypes")
	private LinkedList addList = new LinkedList();
	@SuppressWarnings("unused")
	private JTextField textField_11;
	ResultSet temp;

	private int sorted = 0;
	// Sprache
	private int speak = 1;

	String[] fenstername = { "Kontakt Hinzufügen", "Add Contact",
			"Contactus Addere" };
	String[] bearbeiten = { "Bearbeiten", "Edit", "Emendo" };
	String[] speichern = { "Speichern", "Save", "Salvum" };
	String[] hinzufuegen = { "Hinzufügen", "Add", "Addere" };
	String[] erstellen = { "Kontakt Erstellen", "Create Contact",
			"Creare Contactus" };

	String[] name = { "Name", "name", "nomen" };
	String[] vorname = { "Vorname", "first name", "praenomen" };
	String[] strasse = { "Strasse", "street", "via" };
	String[] plz = { "PLZ", "postcode", "territorium" };
	String[] stadt = { "Stadt", "street", "oppidum" };
	String[] email = { "E-Mail", "mail", "technicus nuntius" };
	String[] geburtstag = { "Geburtstag", "birthday", "diei natalis" };
	String[] telefonHeim = { "Telefon (Heim)", "phone (home)",
			"telephonum (patria)" };
	String[] telefonArbeit = { "Telefon (Arbeit)", "phone (work)",
			"telephonum (opus)" };
	String[] telefonMobil = { "Telefon (Mobil)", "mobile", "telephonum manuale" };
	String[] telefonFax = { "FAX", "fax", "mutoniatus" };

	/**
	 * Gibt RS mit allen Kontakten des Users aus DB zurueck, sortiert nach
	 * Vorname
	 * 
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getContactsFromDb() throws SQLException {

		Statement stat = connection.createStatement();

		// String franz = "'franz'";

		stat.executeQuery("use `sopra-ws12-awesome_fireballs`;");

		ResultSet rs = stat
				.executeQuery("SELECT Adress.* FROM Adress, Entrys, Books, User where Adress.idAdress = Entrys.idAdress AND Entrys.idBooks = Books.idBooks AND Books.idUser = User.idUser AND User.idUser="
						+ Test.iduser
						+ " GROUP BY Adress.idAdress ORDER BY Adress.fname;");

		return rs;

	}

	/**
	 * Gibt RS mit allen Kontakten des Users aus DB zurueck, sortiert nach
	 * Nachname
	 * 
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getContactsFromDbLName() throws SQLException {

		Statement stat = connection.createStatement();

		// String franz = "'franz'";

		stat.executeQuery("use `sopra-ws12-awesome_fireballs`;");

		ResultSet rs = stat
				.executeQuery("SELECT Adress.* FROM Adress, Entrys, Books, User where Adress.idAdress = Entrys.idAdress AND Entrys.idBooks = Books.idBooks AND Books.idUser = User.idUser AND User.idUser="
						+ Test.iduser
						+ " GROUP BY Adress.idAdress ORDER BY Adress.lname;");

		return rs;

	}

	/**
	 * Im Konstruktor wird JFrame erstellt
	 * 
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	public AddContactWindow(final Object openedfrom, final boolean copy) {

		or = new OptionReader();
		try {
			or.getOpionsFromFile();
		} catch (IOException e2) {

			e2.printStackTrace();
		}

		String windowColors = or.getColorSetting();
		windowColorsSeperated = windowColors.split(",");
		r = Integer.parseInt(windowColorsSeperated[0]);
		g = Integer.parseInt(windowColorsSeperated[1]);
		b = Integer.parseInt(windowColorsSeperated[2]);
		speak = Integer.parseInt(or.getLanguage());
		sorted = Integer.parseInt(or.getConOpt1());

		setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
		setTitle(fenstername[speak]);
		int openedfromid = 0;

		if (openedfrom != null && openedfrom instanceof ToDoPane) {
			openedfromid = ((ToDoPane) openedfrom).getGlobalselectedid();
		} else if (openedfrom != null && openedfrom instanceof NewMailWindow
				&& copy == false) {
			schondabeimail = ((NewMailWindow) openedfrom).getllan();
		} else if (openedfrom != null && openedfrom instanceof NewMailWindow
				&& copy == true) {
			schondabeimail = ((NewMailWindow) openedfrom).getllcopy();
		} else if (openedfrom != null && openedfrom instanceof NewEventWindow) {
			openedfromid = ((NewEventWindow) openedfrom).getIdEvents();
		} else if (openedfrom != null && openedfrom instanceof DayView) {
			openedfromid = ((DayView) openedfrom).getIdEvents();
		} else {
			@SuppressWarnings("unused")
			Object hans = null;
		}

		Dimension size = new Dimension(800, 500);
		setSize(size);
		setPreferredSize(size);
		setMinimumSize(size);
		setMaximumSize(size);

		setBackground(Color.LIGHT_GRAY);
		setLayout(new BorderLayout(0, 0));

		JPanel westpanel = new JPanel();
		westpanel.setBackground(new Color(r, g, b));
		add(westpanel, BorderLayout.WEST);

		// CheckBoxList um Personen Checkbox Objekte hinzuzufuegen
		final CheckBoxList contactlist = new CheckBoxList();
		contactlist.setVisibleRowCount(18);
		final DefaultListModel dlm = new DefaultListModel();

		ResultSet contacts = null;
		try {
			// Holt ResultSet mit allen Kontakten aus DB
			if (sorted == 0) {
				contacts = getContactsFromDb();
			}
			if (sorted == 1) {
				contacts = getContactsFromDbLName();
			}

			if (openedfromid != 0 && openedfrom instanceof ToDoPane) {
				Statement stat = connection.createStatement();

				stat.executeQuery("use `sopra-ws12-awesome_fireballs`;");

				ResultSet ischecked = stat
						.executeQuery("SELECT participantsToDo.idAdress FROM participantsToDo WHERE idTask = "
								+ openedfromid + ";");

				while (ischecked.next()) {
					schondabei.add(ischecked.getString(1));
				}
			} else if (openedfromid != 0 && openedfrom instanceof DayView) {
				Statement stat = connection.createStatement();

				stat.executeQuery("use `sopra-ws12-awesome_fireballs`;");

				ResultSet ischecked = stat
						.executeQuery("SELECT participants.idAdress FROM participants WHERE idEvents = "
								+ openedfromid + ";");

				while (ischecked.next()) {
					schondabei.add(ischecked.getString(1));
				}
			}

			while (contacts.next()) {

				// Erstellt fuer jede Person Person Objekt um
				// JCheckBoxWithObject zu Erstellen und nutzt deren Person
				// toString() um Checkbox in der Liste zu betiteln
				Person person = new Person(contacts.getString("lname"),
						contacts.getString("fname"));
				person.setUserID(contacts.getString("idAdress"));

				// Fuegt alle Personen interner Liste zu um spaeter wieder auf
				// id
				// zurueckgreifen zu koennen.
				contactlinkedlist.add(person);

				JCheckBoxWithObject check = new JCheckBoxWithObject(person);
				// Fuegt JCheckBoxWithObject Elemente in Liste ein
				if (openedfrom != null && openedfrom instanceof NewMailWindow) {
					for (int i = 0; i < schondabeimail.size(); i++) {
						if (person.getUserID().equals(
								schondabeimail.get(i).getUserID())) {
							check.setSelected(true);
						}
					}
				} else {
					for (int i = 0; i < schondabei.size(); i++) {
						if (person.getUserID().equals(schondabei.get(i))) {
							check.setSelected(true);
						}
					}
				}

				dlm.addElement(check);

			}

		} catch (SQLException e) {

			e.printStackTrace();
		}

		temp = contacts;

		// MouseListener, welcher fuer die Anzeige der Detailinformationen zu
		// jeder Person sorgt
		ListSelectionListener selectionListener = new ListSelectionListener() {
			public void valueChanged(ListSelectionEvent e) {

				int index = contactlist.getSelectedIndex();

				// Findet UserId von aktuell gewaehltem Kontakt aus der
				// Liste
				String userid = contactlinkedlist.get(index).getUserID();
				// userid to Int um Person an richtige stelle im RS zu
				// finden
				int useridint = Integer.parseInt(userid);

				try {

					temp.absolute(1);

					if (temp.getInt("idAdress") == useridint) {
						textField.setText(temp.getString("lname"));
						textField_10.setText(temp.getString("fname"));
						textField_1.setText(temp.getString("street"));
						textField_2.setText(temp.getString("zip"));
						textField_3.setText(temp.getString("city"));
						textField_4.setText(temp.getString("email"));
						textField_5.setText(temp.getString("bday"));
						textField_6.setText(temp.getString("telehome"));
						textField_7.setText(temp.getString("telework"));
						textField_8.setText(temp.getString("cell"));
						textField_9.setText(temp.getString("fax"));

					}

					while (temp.next()) {

						// System.out.println(temp.getInt("idAdress"));
						if (temp.getInt("idAdress") == useridint) {

							// System.out.println(temp.getInt("idAdress"));

							// Setzt alle Textfelder auf die verfuegbaren
							// Infos des
							// Kontaktes
							textField.setText(temp.getString("lname"));
							textField_10.setText(temp.getString("fname"));
							textField_1.setText(temp.getString("street"));
							textField_2.setText(temp.getString("zip"));
							textField_3.setText(temp.getString("city"));
							textField_4.setText(temp.getString("email"));
							textField_5.setText(temp.getString("bday"));
							textField_6.setText(temp.getString("telehome"));
							textField_7.setText(temp.getString("telework"));
							textField_8.setText(temp.getString("cell"));
							textField_9.setText(temp.getString("fax"));
						}
					}

					// temp.absolute(useridint);
					// System.out.println(temp.getString("idAdress"));

				} catch (SQLException e1) {

					e1.printStackTrace();
				}

			}

		};

		// Liste wird DLM und Listener angehaengt
		contactlist.setModel(dlm);
		contactlist.addListSelectionListener(selectionListener);

		JScrollPane scrollPane = new JScrollPane(contactlist);
		westpanel.add(scrollPane);

		// Zweiten Panel wird erstellt, hierin ist die Detailansicht enthalten
		JPanel centerpanel = new JPanel();
		centerpanel.setBackground(new Color(r, g, b));
		add(centerpanel, BorderLayout.CENTER);
		centerpanel.setLayout(null);

		// Labelfelder zu den zugehoerigen Textfeldern werden erstellt
		JLabel lblName = new JLabel(name[speak]);
		lblName.setHorizontalAlignment(SwingConstants.RIGHT);
		lblName.setBounds(50, 30, 120, 16);
		centerpanel.add(lblName);

		JLabel lblStrae = new JLabel(strasse[speak]);
		lblStrae.setHorizontalAlignment(SwingConstants.RIGHT);
		lblStrae.setBounds(50, 58, 120, 16);
		centerpanel.add(lblStrae);

		JLabel lblPlz = new JLabel(plz[speak]);
		lblPlz.setHorizontalAlignment(SwingConstants.RIGHT);
		lblPlz.setBounds(50, 86, 120, 16);
		centerpanel.add(lblPlz);

		JLabel lblStadt = new JLabel(stadt[speak]);
		lblStadt.setHorizontalAlignment(SwingConstants.RIGHT);
		lblStadt.setBounds(50, 114, 120, 16);
		centerpanel.add(lblStadt);

		JLabel lblEmail = new JLabel(email[speak]);
		lblEmail.setHorizontalAlignment(SwingConstants.RIGHT);
		lblEmail.setBounds(50, 142, 120, 16);
		centerpanel.add(lblEmail);

		JLabel lblGeburtstag = new JLabel(geburtstag[speak]);
		lblGeburtstag.setHorizontalAlignment(SwingConstants.RIGHT);
		lblGeburtstag.setBounds(50, 170, 120, 16);
		centerpanel.add(lblGeburtstag);

		JLabel lblTelefonheim = new JLabel(telefonHeim[speak]);
		lblTelefonheim.setHorizontalAlignment(SwingConstants.RIGHT);
		lblTelefonheim.setBounds(50, 198, 120, 16);
		centerpanel.add(lblTelefonheim);

		JLabel lblTelefonarbeit = new JLabel(telefonArbeit[speak]);
		lblTelefonarbeit.setHorizontalAlignment(SwingConstants.RIGHT);
		lblTelefonarbeit.setBounds(50, 226, 120, 16);
		centerpanel.add(lblTelefonarbeit);

		JLabel lblFax = new JLabel(telefonFax[speak]);
		lblFax.setHorizontalAlignment(SwingConstants.RIGHT);
		lblFax.setBounds(50, 282, 120, 16);
		centerpanel.add(lblFax);

		JLabel lblTelefonmobil = new JLabel(telefonMobil[speak]);
		lblTelefonmobil.setHorizontalAlignment(SwingConstants.RIGHT);
		lblTelefonmobil.setBounds(50, 254, 120, 16);
		centerpanel.add(lblTelefonmobil);

		JLabel lblVorname = new JLabel(vorname[speak]);
		lblVorname.setBounds(309, 30, 71, 16);
		centerpanel.add(lblVorname);

		// Detail Textein/ausgabe Felder werden erstellt

		// Name
		textField = new JTextField();
		textField.setBounds(180, 24, 117, 28);
		centerpanel.add(textField);
		textField.setColumns(10);

		// Strasse
		textField_1 = new JTextField();
		textField_1.setBounds(180, 52, 344, 28);
		centerpanel.add(textField_1);
		textField_1.setColumns(10);

		// PLZ
		textField_2 = new JTextField();
		textField_2.setColumns(10);
		textField_2.setBounds(180, 80, 344, 28);
		centerpanel.add(textField_2);

		// Stadt
		textField_3 = new JTextField();
		textField_3.setColumns(10);
		textField_3.setBounds(180, 108, 344, 28);
		centerpanel.add(textField_3);

		// Email
		textField_4 = new JTextField();
		textField_4.setColumns(10);
		textField_4.setBounds(180, 136, 344, 28);
		centerpanel.add(textField_4);

		// Geb
		textField_5 = new JTextField();
		textField_5.setColumns(10);
		textField_5.setBounds(180, 164, 344, 28);
		centerpanel.add(textField_5);

		// Tele Heim
		textField_6 = new JTextField();
		textField_6.setColumns(10);
		textField_6.setBounds(180, 192, 344, 28);
		centerpanel.add(textField_6);

		// Tele Work
		textField_7 = new JTextField();
		textField_7.setColumns(10);
		textField_7.setBounds(180, 220, 344, 28);
		centerpanel.add(textField_7);

		// Tele Mobil
		textField_8 = new JTextField();
		textField_8.setColumns(10);
		textField_8.setBounds(180, 248, 344, 28);
		centerpanel.add(textField_8);

		// Fax
		textField_9 = new JTextField();
		textField_9.setColumns(10);
		textField_9.setBounds(180, 276, 344, 28);
		centerpanel.add(textField_9);

		// Vorname
		textField_10 = new JTextField();
		textField_10.setBounds(382, 24, 142, 28);
		centerpanel.add(textField_10);
		textField_10.setColumns(10);

		textField.setEditable(false);
		textField_1.setEditable(false);
		textField_2.setEditable(false);
		textField_3.setEditable(false);
		textField_4.setEditable(false);
		textField_5.setEditable(false);
		textField_6.setEditable(false);
		textField_7.setEditable(false);
		textField_8.setEditable(false);
		textField_9.setEditable(false);
		textField_10.setEditable(false);

		// Buttons werden erstellt

		JButton btnSpeichern = new JButton(hinzufuegen[speak]);
		if (openedfrom instanceof Boolean) {
			btnSpeichern.setText("export");
		}
		btnSpeichern.setBounds(407, 431, 117, 29);
		ActionListener addtoMail = new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent arg0) {
				for (int i = 0; i < dlm.size(); i++) {
					JCheckBoxWithObject temp = (JCheckBoxWithObject) dlm.get(i);
					if (temp.isSelected() == true) {
						Person temp2 = (Person) temp.getObject();
						addList.add(temp2.getUserID());
					}

				}
				System.out.println(addList.toString());

				if (openedfrom != null && openedfrom instanceof ToDoPane) {
					((ToDoPane) openedfrom).setParticipants(addList);
					((ToDoPane) openedfrom).setParticipantsText();

				} else if (openedfrom != null
						&& openedfrom instanceof NewMailWindow && copy == false) {
					((NewMailWindow) openedfrom).setllan(getPersonList());
					((NewMailWindow) openedfrom).setEingabeanfromll();
				} else if (openedfrom != null
						&& openedfrom instanceof NewMailWindow && copy == true) {
					((NewMailWindow) openedfrom).setllcopy(getPersonList());
					((NewMailWindow) openedfrom).setEingabekopiefromll();
				} else if (openedfrom != null
						&& openedfrom instanceof NewEventWindow) {
					// hhhhjkhj
					((NewEventWindow) openedfrom).setParticipants(addList);
					((NewEventWindow) openedfrom).setParticipantsText();

				} else if (openedfrom != null && openedfrom instanceof DayView) {
					// hhhhjkhj
					((DayView) openedfrom).setParticipants(addList);
					((DayView) openedfrom).setParticipantsText();

				} else if (openedfrom != null
						&& openedfrom instanceof NewTaskWindow) {
					// hhhhjkhj
					((NewTaskWindow) openedfrom).setParticipants(addList);
					((NewTaskWindow) openedfrom).setParticipantsText();

				} else if (openedfrom != null && openedfrom instanceof Boolean) {
					// hhhhjkhj
					Adressbuch addbuch = new Adressbuch("Export");
					addbuch.setToexport(addList);
					addbuch.addexportstoadressbook();
					File pathFile;
					String savePath = "";
					JFileChooser chooser = new JFileChooser();
					chooser.setCurrentDirectory(new java.io.File("user.home"));
					chooser.setDialogTitle("Speichern unter..");
					chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
					chooser.setAcceptAllFileFilterUsed(false);

					if (chooser.showOpenDialog(null) == JFileChooser.APPROVE_OPTION) {
						pathFile = chooser.getSelectedFile();
						savePath = pathFile.getAbsolutePath();
						System.out.println(savePath);

					} else {
						System.out.println("No Selection ");
					}

					for (int p = 0; p < addbuch.getSize(); p++) {
						addbuch.exportVCard(addbuch.getPerson(p), savePath);
					}
				} else {
					@SuppressWarnings("unused")
					Object hans = null;
				}
				dispose();
			}

		};
		btnSpeichern.addActionListener(addtoMail);
		centerpanel.add(btnSpeichern);

		btnBearbeiten = new JButton(bearbeiten[speak]);
		btnBearbeiten.setBounds(297, 431, 117, 29);
		ActionListener edit = new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				String text = (String) e.getActionCommand();

				if (text.equals(bearbeiten[speak])) {
					btnBearbeiten.setText(hinzufuegen[speak]);
					// die alten textfelder werden in Strings gespeichert
					fname_old = textField_10.getText();
					lname_old = textField.getText();
					street_old = textField_1.getText();
					zip_old = textField_2.getText();
					town_old = textField_3.getText();
					email_old = textField_4.getText();
					bday_old = textField_5.getText();
					telehome_old = textField_6.getText();
					telework_old = textField_7.getText();
					fax_old = textField_9.getText();
					cell_old = textField_8.getText();

					textField.setEditable(true);
					textField_1.setEditable(true);
					textField_2.setEditable(true);
					textField_3.setEditable(true);
					textField_4.setEditable(true);
					textField_5.setEditable(true);
					textField_6.setEditable(true);
					textField_7.setEditable(true);
					textField_8.setEditable(true);
					textField_9.setEditable(true);
					textField_10.setEditable(true);
				}

				if (text.equals(hinzufuegen[speak])) {
					textField.setEditable(false);
					textField_1.setEditable(false);
					textField_2.setEditable(false);
					textField_3.setEditable(false);
					textField_4.setEditable(false);
					textField_5.setEditable(false);
					textField_6.setEditable(false);
					textField_7.setEditable(false);
					textField_8.setEditable(false);
					textField_9.setEditable(false);
					textField_10.setEditable(false);

					@SuppressWarnings("rawtypes")
					SwingWorker worker = new SwingWorker() {
						@Override
						public Object doInBackground() {

							Statement stat = null;
							try {
								stat = connection.createStatement();
								// der inhalt der Textfelder wird in Strings
								// gespeichert
								String fname = textField_10.getText();
								String lname = textField.getText();
								String street = textField_1.getText();
								String zip = textField_2.getText();
								String town = textField_3.getText();
								String email = textField_4.getText();
								String bday = textField_5.getText();
								String telehome = textField_6.getText();
								String telework = textField_7.getText();
								String fax = textField_9.getText();
								String cell = textField_8.getText();

								stat.executeQuery("use `sopra-ws12-awesome_fireballs`;");

								// es wird ein vorgefertigtes update statement
								// erstellt und anschließen werden in die
								// jeweiligen fragezeichen, die Strings
								// eingefügt.
								connection.setAutoCommit(false);
								PreparedStatement pstmt = connection
										.prepareStatement("UPDATE `sopra-ws12-awesome_fireballs`.`Adress` SET `fname`=?, `lname`=?, `street`=?, `zip`=?, `city`=?, `email`=?, `bday`=?, `telehome`=?, `telework`=?, `fax`=?, `cell`=? WHERE `fname`=? AND `lname`=? AND `street`=? AND `zip`=? AND `city`=? AND `email`=? AND `bday`=? AND `telehome`=? AND `telework`=? AND `fax`=? AND `cell`=?;");
								pstmt.setString(1, fname);
								pstmt.setString(2, lname);
								pstmt.setString(3, street);
								pstmt.setString(4, zip);
								pstmt.setString(5, town);
								pstmt.setString(6, email);
								pstmt.setString(7, bday);
								pstmt.setString(8, telehome);
								pstmt.setString(9, telework);
								pstmt.setString(10, fax);
								pstmt.setString(11, cell);
								pstmt.setString(12, fname_old);
								pstmt.setString(13, lname_old);
								pstmt.setString(14, street_old);
								pstmt.setString(15, zip_old);
								pstmt.setString(16, town_old);
								pstmt.setString(17, email_old);
								pstmt.setString(18, bday_old);
								pstmt.setString(19, telehome_old);
								pstmt.setString(20, telework_old);
								pstmt.setString(21, fax_old);
								pstmt.setString(22, cell_old);
								pstmt.addBatch();

								// System.out.println(pstmt.toString());
								// das vorgefertigte statement wird ausgeführt
								pstmt.executeBatch();
								connection.commit();
								connection.setAutoCommit(true);
							} catch (SQLException e) {
								e.printStackTrace();
							}
							return stat;

						}

						@Override
						public void done() {
							btnBearbeiten.setText(bearbeiten[speak]);

							ResultSet contacts = null;
							contactlinkedlist.clear();
							try {
								// Holt ResultSet mit allen Kontakten aus DB
								if (sorted == 0) {
									contacts = getContactsFromDb();
								}
								if (sorted == 1) {
									contacts = getContactsFromDbLName();
								}
								int i = 0;
								while (contacts.next()) {

									// Erstellt fuer jede Person Person Objekt
									// um
									// JCheckBoxWithObject zu Erstellen und
									// nutzt deren Person
									// toString() um Checkbox in der Liste zu
									// betiteln
									Person person = new Person(
											contacts.getString("lname"),
											contacts.getString("fname"));
									person.setUserID(contacts
											.getString("idAdress"));

									// Fuegt alle Personen interner Liste zu um
									// spaeter wieder auf
									// id
									// zurueckgreifen zu koennen.
									contactlinkedlist.add(person);

									JCheckBoxWithObject check = new JCheckBoxWithObject(
											person);
									// Fuegt JCheckBoxWithObject Elemente in
									// Liste ein
									dlm.set(i, check);
									i++;

								}

								temp = contacts;

							} catch (SQLException e) {

								e.printStackTrace();
							}

						}
					};
					worker.execute();

				}

			}

		};
		btnBearbeiten.addActionListener(edit);
		centerpanel.add(btnBearbeiten);

		JButton btnKontaktHinzufgen = new JButton(erstellen[speak]);

		btnKontaktHinzufgen.setBounds(6, 431, 175, 29);

		// Action Listener zugehoerig zum AddKontakt Button wird erstellt,
		// dieser oeffnet ein neues NewContactWindow mit welchem ein neuer
		// Kontakt hinzugefuegt werden kann
		ActionListener addKontakt = new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent arg0) {

				JFrame ncw = new NewContactWindow();
				ncw.setVisible(true);

			}

		};
		btnKontaktHinzufgen.addActionListener(addKontakt);
		centerpanel.add(btnKontaktHinzufgen);

		setVisible(true);

	}

	/**
	 * Gibt Liste von Personen zurück
	 * 
	 * @return LinkedList mit Personen
	 */
	public LinkedList<Person> getPersonList() {
		String orquery = "";
		for (int i = 0; i < addList.size(); i++) {
			orquery += "idAdress = ";
			orquery += addList.get(i).toString();
			if (i != addList.size() - 1) {
				orquery += " OR ";
			}
		}
		Statement stat = null;
		ResultSet emails = null;

		try {
			stat = connection.createStatement();
			stat.executeQuery("use `sopra-ws12-awesome_fireballs`;");
			emails = stat
					.executeQuery("SELECT * FROM `sopra-ws12-awesome_fireballs`.Adress WHERE "
							+ orquery + " ;");
		} catch (SQLException e) {
			JOptionPane
					.showMessageDialog(
							null,
							"Es konnte keine Verbindung zur Datenbank hergestellt werden",
							"Haaalt! Stop!", JOptionPane.ERROR_MESSAGE);
			e.printStackTrace();
		}

		LinkedList<Person> selected = new LinkedList<Person>();
		try {
			while (emails.next()) {
				Person temp = new Person(emails.getString("lname"),
						emails.getString("fname"), emails.getString("email"),
						emails.getString("idAdress"));
				selected.add(temp);
			}
		} catch (SQLException e) {
			JOptionPane
					.showMessageDialog(
							null,
							"Es konnte keine Verbindung zur Datenbank hergestellt werden",
							"Haaalt! Stop!", JOptionPane.ERROR_MESSAGE);
			e.printStackTrace();
		}

		return selected;
	}

	public void setPersonList(LinkedList<Person> personList) {
		this.personList = personList;
	}
}